package com.abl.common.util;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Optional;
import java.util.function.Consumer;

@Slf4j
public class DBKit {

//     MySQL 8.0 以下版本
//        static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";//驱动类的类路径
//        static final String DB_URL = "jdbc:mysql://localhost:3306/db_demo?serverTimezone=Asia/Shanghai&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useUnicode=true&useSSL=false&useTimezone=false&allowPublicKeyRetrieval=true";//数据库的连接地址

//     MySQL 8.0 以上版本
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
//    static final String DB_URL = "jdbc:p6spy:mysql://192.168.1.194:3306/position_sdk?serverTimezone=Asia/Shanghai&characterEncoding=UTF8&zeroDateTimeBehavior=CONVERT_TO_NULL&useUnicode=true&useSSL=false&useTimezone=false";
//    static final String ACCOUNT = "root";
//    static final String PASSWORD = "eHIGH2014";

//    public static Connection connection() {
//        Connection conn = null;
//        try {
//            Class.forName(JDBC_DRIVER);
//        } catch (ClassNotFoundException e) {
//            e.printStackTrace();
//        }
//        try {
//            conn = DriverManager.getConnection(DB_URL, ACCOUNT, PASSWORD);
//        } catch (SQLException throwable) {
//            throwable.printStackTrace();
//        }
//        return conn;
//    }

    public static Connection getConnection(String DB_URL, String ACCOUNT, String PASSWORD) {
        Connection conn = null;
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(DB_URL, ACCOUNT, PASSWORD);
        } catch (SQLException throwable) {
            throwable.printStackTrace();
        }
        return conn;
    }

    public void execute(String sql, Connection connection, Consumer<ResultSet> consumer) throws SQLException {
        log.debug("执行SQL:{}", sql);
        int count = 0;
        long start = System.nanoTime();
        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                consumer.accept(resultSet);
                count++;
            }
            long end = System.nanoTime();
            log.debug("返回记录数:{},耗时(ms):{}", count, (end - start) / 1000000);
        }
    }


    public static void closeConnection(Connection connection) {
        Optional.ofNullable(connection).ifPresent((con) -> {
            try {
                con.close();
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            }
        });
    }


    public static List<HashMap<String, String>> getTables(Connection connection) throws Exception {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        List<HashMap<String, String>> tables = new ArrayList<>();
        try (ResultSet resultSet = databaseMetaData.getTables("abl", null, null, new String[]{"TABLE", "VIEW"})) {
            while (resultSet.next()) {
                HashMap<String, String> table = new HashMap<>();
                String table_name = resultSet.getString("TABLE_NAME");
                String remarks = resultSet.getString("REMARKS");
                String table_type = resultSet.getString("TABLE_TYPE");
                table.put("table_name", table_name);
                table.put("remarks", remarks);
                table.put("table_type", table_type);
                tables.add(table);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return tables;
    }

}
